import 'dart:async';

import 'package:logger/logger.dart';
import 'package:pt_manage/db/query/query_page.dart';
import 'package:sqflite/sqflite.dart';

import '../query/query_config.dart';
import '../util/db_util.dart';

abstract class DaoBase<T> {

  Database? db;
  final String tableName;
  final logger = Logger();
  final String dbName;
  final String createSql;

  DaoBase({this.dbName = "notes", required this.tableName, required this.createSql});

  Future close() async {
    if (db != null && db!.isOpen) await DbUtil.close(db!);
  }

  int getId(T obj);
  Map<String, dynamic> toJson(T obj);
  T fromJson(Map<dynamic, dynamic> json);

  Future<Database> _getDb() async => db ??= await DbUtil.open(dbName, createSql);

  Future<T> insert(T obj) async {
    await _getDb().then((db) => db.insert(tableName, toJson(obj)));
    return obj;
  }

  Future<T?> getById(int id, {columns}) async {
    List<Map> maps = await _getDb().then((db) => db.query(tableName, columns: columns, where: "id = ?", whereArgs: [id]));
    if (maps.isNotEmpty) return fromJson(maps.first);
    return null;
  }

  Future<int> removeById(int id) async {
    return await _getDb().then((db) => db.delete(tableName, where: 'id = ?', whereArgs: [id]));
  }

  Future<int> update(T obj) async {
    return await _getDb().then((db) => db.update(tableName, (obj as dynamic).toJson(), where: 'id = ?', whereArgs: [getId(obj)]));
  }

  Future<List<T>?> all({columns}) async {
    List<Map> maps = await _getDb().then((db) => db.query(tableName, columns: columns));
    if (maps.isNotEmpty) return maps.map((e) => fromJson(e)).toList();
    return null;
  }

  int? _getOffset(int? page, int? pageSize) {
    if (page == null || pageSize == null) return null;
    return (page - 1) * pageSize;
  }

  Future<List<T>?> list({columns, groupBy, orderBy, page, pageSize}) async {
    List<Map> maps = await _getDb().then((db) async => await db.query(tableName, columns: columns, groupBy: groupBy, orderBy: orderBy, limit: pageSize, offset: _getOffset(page, pageSize)));
    if (maps.isNotEmpty) return maps.map((e) => fromJson(e)).toList();
    return null;
  }

  Future<List<T>?> listQuery(QueryConfig config, {columns, groupBy, orderBy, page, pageSize}) async {
    return await listQueryList([config], columns: columns, groupBy: groupBy, page: page, pageSize: pageSize);
  }

  Future<List<T>?> listQueryList(List<QueryConfig> configs, {columns, groupBy, orderBy, page, pageSize}) async {
    return await listWhere(QueryConfigWhere.of(configs), columns: columns, groupBy: groupBy, page: page, pageSize: pageSize);
  }

  Future<List<T>?> listWhere(QueryConfigWhere where, {columns, groupBy, orderBy, page, pageSize}) async {
    List<Map> maps = await _getDb().then((db) async => await db.query(tableName, columns: columns, groupBy: groupBy, orderBy: orderBy, where: where.where, whereArgs: where.args, limit: pageSize, offset: _getOffset(page, pageSize)));
    if (maps.isNotEmpty) return maps.map((e) => fromJson(e)).toList();
    return null;
  }

  Future<QueryPage<T>> page(QueryPage<T> page, {columns, groupBy, orderBy}) async {
    return page..total = await count()..data = await list(columns: columns, page: page.page, pageSize: page.pageSize, groupBy: groupBy, orderBy: orderBy) ?? [];
  }

  Future<QueryPage<T>> pageQuery(QueryPage<T> page, QueryConfig config, {columns, groupBy, orderBy}) async {
    return pageQueryList(page, [config], columns: columns, groupBy: groupBy, orderBy: orderBy);
  }

  Future<QueryPage<T>> pageQueryList(QueryPage<T> page, List<QueryConfig> configs, {columns, groupBy, orderBy}) async {
    return pageWhere(page, QueryConfigWhere.of(configs), columns: configs, groupBy: groupBy, orderBy: orderBy);
  }

  Future<QueryPage<T>> pageWhere(QueryPage<T> page, QueryConfigWhere where, {columns, groupBy, orderBy}) async {
    return page..total = await countWhere(where)..data = await listWhere(where, columns: columns, page: page.page, pageSize: page.pageSize, groupBy: groupBy, orderBy: orderBy) ?? [];
  }

  Future<T?> getOneQuery(QueryConfig config, {ignoreMultiple = false}) async {
    return getOneQueryList([config], ignoreMultiple: ignoreMultiple);
  }

  Future<T?> getOneQueryList(List<QueryConfig> configs, {ignoreMultiple = false}) async {
    return getOneWhere(QueryConfigWhere.of(configs), ignoreMultiple: ignoreMultiple);
  }

  Future<T?> getOneWhere(QueryConfigWhere where, {ignoreMultiple = false}) async {
    var list = await listWhere(where);
    if (list == null || list.isEmpty) return null;
    if (list.length > 1 && !ignoreMultiple) throw "Multiple results found";
    return list.first;
  }

  static const countSql = "select count(1) as count from ";

  Future<int> count() async {
    List<Map> maps = await _getDb().then((db) => db.rawQuery("$countSql $tableName"));
    return maps.first['count'] as int;
  }

  Future<int> countQuery(QueryConfig config) async {
    QueryConfigWhere queryConfigWhere = QueryConfigWhere.of([config]);
    List<Map> maps = await _getDb().then((db) => db.rawQuery("$countSql $tableName where ${queryConfigWhere.where}", queryConfigWhere.args));
    return maps.first['count'] as int;
  }

  Future<int> countQueryList(List<QueryConfig> config) async {
    QueryConfigWhere queryConfigWhere = QueryConfigWhere.of(config);
    List<Map> maps = await _getDb().then((db) => db.rawQuery("$countSql $tableName where ${queryConfigWhere.where}", queryConfigWhere.args));
    return maps.first['count'] as int;
  }

  Future<int> countWhere(QueryConfigWhere where) async {
    List<Map> maps = await _getDb().then((db) => db.rawQuery("$countSql $tableName where ${where.where}", where.args));
    return maps.first['count'] as int;
  }

  Future<T?> getJoinOne() async {
    return null;
  }
  
  Future<bool> exist() async {
    return await count() > 0;
  }

  Future<bool> existQuery(QueryConfig config) async {
    return await countQuery(config) > 0;
  }

  Future<bool> existQueryList(List<QueryConfig> config) async {
    return await countQueryList(config) > 0;
  }

  Future<bool> existWhere(QueryConfigWhere where) async {
    return await countWhere(where) > 0;
  }

}